﻿Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Collections
Imports extranet.BE
Imports MySql.Data.MySqlClient
Imports MySql.Data.MySqlClient.MySqlDbType

Public Class DACandidato
  Dim objBD As New BancoDeDados
  Public objQuery As New BEQuery
  Private _conexao As MySqlConnection = Nothing

  Public Sub New()
    _conexao = BancoDeDados.ConnectionInstance()
  End Sub

  Public Sub New(ByVal booCondicaoSimilar As Boolean)
    objQuery.Where.CondicaoSimilar = booCondicaoSimilar
  End Sub

  Public Function listarNacionalidadesCandidatos() As List(Of String)
    Dim Nacionalidades As New List(Of String)
    Dim consultaSql As String = "SELECT DISTINCT nacionalidade FROM ext_candidatos WHERE nacionalidade IS NOT NULL"
    Try
      Using command As New MySqlCommand(consultaSql, _conexao)
        Using reader As MySqlDataReader = command.ExecuteReader()
          While reader.Read()
            Nacionalidades.Add(reader(0))
          End While
        End Using
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.CandidatoDAO.listarNacionalidadesCandidatos(): " & ex.Message)
    End Try
    Return Nacionalidades
  End Function

  Public Function listarCidadesCandidatos() As List(Of String)
    Dim Cidades As New List(Of String)
    Dim consultaSql As String = "SELECT DISTINCT cidade FROM ext_candidatos WHERE cidade IS NOT NULL"
    Try
      Using command As New MySqlCommand(consultaSql, _conexao)
        Using reader As MySqlDataReader = command.ExecuteReader()
          While reader.Read()
            Cidades.Add(reader(0))
          End While
        End Using
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.CandidatoDAO.listarCidadesCandidatos(): " & ex.Message)
    End Try
    Return Cidades
  End Function

  Public Function listarEstadosCandidatos() As List(Of String)
    Dim Estados As New List(Of String)
    Dim consultaSql As String = "SELECT DISTINCT estado FROM ext_candidatos WHERE estado IS NOT NULL"
    Try
      Using command As New MySqlCommand(consultaSql, _conexao)
        Using reader As MySqlDataReader = command.ExecuteReader()
          While reader.Read()
            Estados.Add(reader(0))
          End While
        End Using
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.CandidatoDAO.listarEstadosCandidatos(): " & ex.Message)
    End Try
    Return Estados
  End Function

  Public Function listarEscolaridadesCandidatos() As List(Of String)
    Dim GrauEscolaridade As New List(Of String)
    Dim consultaSql As String = "SELECT DISTINCT escolaridade FROM ext_formacoes_escolares;"
    Try
      Using command As New MySqlCommand(consultaSql, _conexao)
        Using reader As MySqlDataReader = command.ExecuteReader()
          While reader.Read()
            GrauEscolaridade.Add(reader(0).ToString().Replace("_", " "))
          End While
        End Using
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.CandidatoDAO.listarEscolaridadeeCandidatos(): " & ex.Message)
    End Try
    Return GrauEscolaridade
  End Function

  Public Function listar() As List(Of BECandidato)
    Dim candidatos As New List(Of BECandidato)
    Dim candidato As BECandidato
    Dim consultaSql As String = "SELECT * FROM ext_candidatos"
    Try
      Using command As New MySqlCommand(consultaSql, _conexao)
        Using reader As MySqlDataReader = command.ExecuteReader()
          While reader.Read()
            candidato = LerCampos(reader)
          End While
        End Using
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.CandidatoDAO.listar(): " & ex.Message)
    End Try
    Return candidatos
  End Function

  Public Function selecionaCandidato(ByVal candidato As BECandidato) As BECandidato
    Dim consultaSql As String = "SELECT * FROM ext_candidatos WHERE id = @candidatoId"
    Try
      Using command As New MySqlCommand(consultaSql, _conexao)
        command.Parameters.AddWithValue("@candidatoId", candidato.Id)
        Using reader As MySqlDataReader = command.ExecuteReader()
          While reader.Read()
            candidato = LerCampos(reader)
          End While
        End Using
      End Using
      candidato.VagasAssociadas = Me.selecionarVagasCandidato(candidato)
      Dim curriculo As New Curriculo()
      Dim experiencias As List(Of ExperienciaProfissional)
      Dim formacoes As List(Of FormacaoEscolar)

      experiencias = selecionarExperienciaCandidato(candidato)
      formacoes = selecionarFormacoesEscolares(candidato)
      If Not experiencias Is Nothing Then
        curriculo.ExperienciasProfessionais = experiencias
      End If

      If Not formacoes Is Nothing Then
        curriculo.FormacoesEscolares = formacoes
      End If
      curriculo.Idiomas = selecionarIdiomasCandidato(candidato)
      candidato.Curriculo = curriculo
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.CandidatoDAO.selecionar(): " & ex.Message)
    End Try
    Return candidato
  End Function

  Public Function selecionaCandidatos(ByVal objBECandidato As BECandidato) As IList(Of BECandidato)
    Dim objBECandidatos As List(Of BECandidato) = New List(Of BECandidato)
    Try
      objBD.duplicaAspasSimples(objBECandidato, GetType(BECandidato))
      objQuery._Select = "SELECT c.* FROM extranet.ext_candidatos c " & _
                         "LEFT JOIN ext_rel_formacoes_candidato erfc ON c.id=erfc.candidatoid " & _
                         "LEFT JOIN ext_formacoes_escolares efc ON erfc.formacaoescolarid=efc.id " & _
                         "LEFT JOIN ext_rel_exp_candidato erec ON c.id=erec.candidatoid " & _
                         "LEFT JOIN ext_experiencias_professionais eep ON erec.experienciaprofissionalid = eep.id "
      If objBECandidato IsNot Nothing Then
        With objBECandidato
          If .Id > 0 Then objQuery.Where.AdicionarCondicaoWhere("c.id", .Id, False)
          objQuery.Where.AdicionarCondicaoWhere("c.login", .Login)
          objQuery.Where.AdicionarCondicaoWhere("c.nome", .Nome)
          objQuery.Where.AdicionarCondicaoWhere("c.cpf", .Cpf)
          objQuery.Where.AdicionarCondicaoWhere("c.sexo", .Sexo)
          objQuery.Where.AdicionarCondicaoWhere("c.possuifilhos", .PossuiFilhos)
          objQuery.Where.AdicionarCondicaoWhere("c.portadordeficiencia", .PortadorDeficiencia)
          objQuery.Where.AdicionarCondicaoWhere("c.nacionalidade", .Nacionalidade)
          objQuery.Where.AdicionarCondicaoWhere("c.cidade", .Cidade)
          objQuery.Where.AdicionarCondicaoWhere("c.estado", .Estado)
          If .VagasAssociadas IsNot Nothing AndAlso .VagasAssociadas.Count() > 0 Then
            Dim strVagasId As String = ""
            For Each objVaga In .VagasAssociadas
              If objVaga.Id > 0 Then strVagasId &= objVaga.Id & ","
            Next
            If strVagasId.Length() > 0 Then strVagasId = strVagasId.Substring(0, strVagasId.Length() - 1)
            If strVagasId.Length() > 0 Then _
              objQuery.Where.AdicionarCondicaoWhereSubSelect("c.id", _
                                                             "SELECT candidatoId FROM ext_rel_candidato_vagas " & _
                                                             "WHERE vagaid IN (" & strVagasId & ")")
          End If
          If .Curriculo IsNot Nothing Then
            With .Curriculo
              If .FormacoesEscolares IsNot Nothing AndAlso .FormacoesEscolares.Count() > 0 AndAlso _
                 .FormacoesEscolares(0).Escolaridade.ToString().Length() > 0 Then
                objQuery.Where.AdicionarCondicaoWhere("efc.escolaridade", .FormacoesEscolares(0).Escolaridade)
              End If
              If .ExperienciasProfessionais IsNot Nothing AndAlso .ExperienciasProfessionais.Count() > 0 AndAlso _
                 .ExperienciasProfessionais(0).AtribuicoesDoCargo.Length() > 0 Then
                objQuery.Where.AdicionarCondicaoWhere("eep.atribuicoesdocargo", .ExperienciasProfessionais(0).AtribuicoesDoCargo)
              End If
              If .Idiomas IsNot Nothing AndAlso .Idiomas.Count() > 0 Then
                For Each objIdioma As Idioma In .Idiomas
                  objQuery.Where.AdicionarCondicaoWhereSubSelect("c.id", _
                                                                 "SELECT candidatoId FROM ext_rel_idiomas_candidato " & _
                                                                 "WHERE idiomaId=" & objIdioma.Id & " AND nivel>=" & objIdioma.Nivel)
                Next
              End If
            End With
          End If
        End With
      End If
      objBECandidatos = selecionaCandidatos()
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DACandidato.SelecionaCandidatos(): " & ex.Message)
    End Try
    Return objBECandidatos
  End Function

  Private Function selecionaCandidatos() As IList(Of BECandidato)
    Dim objBECandidatos As List(Of BECandidato) = New List(Of BECandidato)
    Try
      Using objSQLCommand As MySqlCommand = New MySqlCommand(objQuery.Query, BancoDeDados.ConnectionInstance())
        Using objSQLDReader As MySqlDataReader = objSQLCommand.ExecuteReader()
          While objSQLDReader.Read()
            objBECandidatos.Add(LerCampos(objSQLDReader))
          End While
        End Using
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.CandidatoDAO.selecionaCandidatos(): " & ex.Message)
    End Try
    Return objBECandidatos
  End Function

  Public Function ContarRespostasCertas(ByVal objBECandidato As BECandidato) As Integer
    Dim intRespostasCertas As Integer = 0
    objQuery._Select = "SELECT count(*) FROM ext_notas_candidato nc " & _
                       "INNER JOIN ext_questoes q ON nc.questaoid=q.id " & _
                       "WHERE candidatoid=" & objBECandidato.Id & _
                       "  AND provaid=" & objBECandidato.VagasAssociadas(0).Prova.Id & _
                       " AND (q.respostaDissertativa=1 OR " & _
                             "nc.alternativaid=(SELECT aq.id FROM ext_alternativas_questao aq " & _
                                               "WHERE nc.questaoid=aq.questaoid AND aq.alternativacerta=1))"
    Try
      Using objSQLCommand As MySqlCommand = New MySqlCommand(objQuery.Query, BancoDeDados.ConnectionInstance())
        Using objSQLDReader As MySqlDataReader = objSQLCommand.ExecuteReader()
          While objSQLDReader.Read()
            intRespostasCertas = objSQLDReader(0)
          End While
        End Using
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.CandidatoDAO.ContarRespostasCertas(): " & ex.Message)
    End Try
    Return intRespostasCertas
  End Function

  Public Function selecionarVagasCandidato(ByVal candidato As BECandidato) As List(Of BEVaga)
    Dim consultaSql As String = "SELECT v.* FROM ext_vagas v " & _
                                "INNER JOIN ext_rel_candidato_vagas cv ON v.id = cv.vagaid AND cv.candidatoid=" & candidato.Id
    Dim vaga As BEVaga
    Dim vagas As New List(Of BEVaga)
    Dim strProvaId As String = ""
    Using command As New MySqlCommand(consultaSql, _conexao)
      Using reader As MySqlDataReader = command.ExecuteReader()
        While reader.Read()
          vaga = New BEVaga(reader("id"))
          vaga.Codigo = reader("codigo")
          vaga.AreaVaga = reader("areaid")
          vaga.Titulo = reader("titulo")
          vaga.Descricao = reader("descricao")
          vaga.Estado = reader("ativo")
          strProvaId = reader("provaid").ToString()
          vaga.Prova = New BEProva(IIf(strProvaId.Length() > 0, strProvaId, 0))
          vagas.Add(vaga)
        End While
      End Using
    End Using
    Return vagas
  End Function

  Public Function selecionarExperienciaCandidato(ByVal candidato As BECandidato) As List(Of ExperienciaProfissional)
    Dim consultaSql As String = "SELECT e.id, e.atribuicoesdocargo, e.cargo,e.dataentrada, e.datasaida, " & _
                                       "e.empregoatual, e.nomedaempresa " & _
                                "FROM ext_experiencias_professionais e " & _
                                "INNER JOIN ext_rel_exp_candidato rec ON e.id=rec.experienciaprofissionalid " & _
                                "INNER JOIN ext_candidatos c on rec.candidatoid=c.id AND c.id=@candidatoid"
    Dim experiencia As ExperienciaProfissional
    Dim experiencias As New List(Of ExperienciaProfissional)
    Using command As New MySqlCommand(consultaSql, _conexao)
      command.Parameters.AddWithValue("@candidatoId", candidato.Id)
      Using reader As MySqlDataReader = command.ExecuteReader()
        While reader.Read()
          experiencia = New ExperienciaProfissional()
          If Not TypeOf (reader(0)) Is System.DBNull Then
            experiencia.Id = reader(0)
          End If
          If Not TypeOf (reader(1)) Is System.DBNull Then
            experiencia.AtribuicoesDoCargo = reader(1)
          End If
          If Not TypeOf (reader(2)) Is System.DBNull Then
            experiencia.Cargo = reader(2)
          End If
          If Not TypeOf (reader(3)) Is System.DBNull Then
            experiencia.DataEntrada = reader(3)
          End If
          If Not TypeOf (reader(4)) Is System.DBNull Then
            experiencia.DataSaida = reader(4)
          End If
          If Not TypeOf (reader(5)) Is System.DBNull Then
            experiencia.EmpregoAtual = Boolean.Parse(reader(5))
          End If
          If Not TypeOf (reader(6)) Is System.DBNull Then
            experiencia.NomeDaEmpresa = reader(6)
          End If
          experiencias.Add(experiencia)
        End While
      End Using
    End Using
    Return experiencias
  End Function

  Public Function selecionarPeloLogin(ByVal objCandidato As BECandidato) As BECandidato
    Dim consultaSql As String = "SELECT * FROM ext_candidatos " & _
                                "WHERE login='" & objCandidato.Login & "' AND " & _
                                      "senha='" & objCandidato.Senha & "'"
    Try
      Using command As New MySqlCommand(consultaSql, _conexao)
        Using reader As MySqlDataReader = command.ExecuteReader()
          While reader.Read()
            objCandidato = LerCampos(reader)
          End While
        End Using
      End Using
      objCandidato.VagasAssociadas = Me.selecionarVagasCandidato(objCandidato)

      Dim experiencias As List(Of ExperienciaProfissional)
      experiencias = selecionarExperienciaCandidato(objCandidato)
      If Not experiencias Is Nothing Then
        Dim curriculo As New Curriculo()
        curriculo.ExperienciasProfessionais = experiencias
        objCandidato.Curriculo = curriculo
      End If
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.CandidatoDAO.selecionar(): " & ex.Message)
    End Try
    Return objCandidato
  End Function

  Private Function LerCampos(ByVal objReader As MySqlDataReader) As BECandidato
    Dim objCandidato As New BECandidato()
    objCandidato.Curriculo = New Curriculo()
    With objCandidato
      .Id = objReader("id")
      .Cep = objReader("cep").ToString()
      .Cidade = objReader("cidade").ToString()
      .Cpf = objReader("cpf").ToString()
      If Not TypeOf (objReader("dataNascimento")) Is System.DBNull Then
        .DataNascimento = objReader("dataNascimento")
      End If
      .Ddd = objReader("ddd").ToString()
      .Email = objReader("email").ToString()
      .Endereco = objReader("endereco").ToString()
      .Estado = objReader("estado").ToString()
      .EstadoCivil = objReader("estadoCivil").ToString()
      .Login = objReader("login").ToString()
      .Nacionalidade = objReader("nacionalidade").ToString()
      .Nome = objReader("nome").ToString()
      .Pais = objReader("pais").ToString()
      .PortadorDeficiencia = objReader("portadorDeficiencia").ToString()
      .PossuiFilhos = objReader("possuiFilhos").ToString()
      .Ramal = objReader("ramal").ToString()
      .Senha = objReader("senha").ToString()
      .Sexo = objReader("sexo").ToString()
      .TelefoneCelular = objReader("telefoneCelular").ToString()
      .TelefoneResidencial = objReader("telefoneResidencial").ToString()
      .Rg = objReader("rg").ToString()
      If Not TypeOf (objReader("curriculoId")) Is System.DBNull Then
        .Curriculo.Id = objReader("curriculoId")
      End If
    End With
    Return objCandidato
  End Function

  Private Sub salvarCurriculoCandidato(ByVal candidato As BECandidato)

    'inserirFormacao
    For Each x In candidato.Curriculo.ExperienciasProfessionais
      If x.Id = -1 Then
        x = Me.inserirExperienciaProfissional(x)
        inserirExperienciaCandidato(x, candidato)
      Else
        Me.atualizarExperienciaProfissionalDoCandidato(x, candidato)
      End If
    Next

    For Each y In candidato.Curriculo.FormacoesEscolares
      If y.Id = -1 Then
        y = Me.inserirFormacaoEscolar(y)
        inserirFormacaoCandidato(y, candidato)
      Else
        atualizarFormacaoEscolarDoCandidato(y, candidato)
      End If
    Next

    atualizarIdiomaDoCandidato(candidato)

  End Sub

  Public Sub atualizarExperienciaProfissionalDoCandidato(ByVal experiencia As ExperienciaProfissional, ByVal candidato As BECandidato)
    Dim consultaSql As String = "update ext_experiencias_professionais set atribuicoesdocargo = @atribuicoesdocargo,cargo = @cargo,dataentrada =@dataentrada,datasaida=@datasaida,empregoatual=@empregoatual,nomedaempresa=@nomedaempresa where id = @id"
    Try
      Using command As New MySqlCommand(consultaSql, _conexao)
        command.Parameters.Add("@atribuicoesdocargo", MySqlDbType.VarChar).Value = experiencia.AtribuicoesDoCargo
        command.Parameters.Add("@cargo", MySqlDbType.VarChar).Value = experiencia.Cargo
        If experiencia.DataEntrada = Date.MinValue Then
          command.Parameters.Add("@dataentrada", Nothing)
        Else
          command.Parameters.AddWithValue("@dataentrada", experiencia.DataEntrada)
        End If
        If experiencia.DataSaida = Date.MinValue Then
          command.Parameters.Add("@datasaida", Nothing)
        Else
          command.Parameters.AddWithValue("@datasaida", experiencia.DataSaida)
        End If
        command.Parameters.Add("@empregoatual", MySqlDbType.VarChar).Value = experiencia.EmpregoAtual
        command.Parameters.Add("@nomedaempresa", MySqlDbType.VarChar).Value = experiencia.NomeDaEmpresa
        command.Parameters.AddWithValue("@id", experiencia.Id)
        command.ExecuteNonQuery()
      End Using
    Catch ex As Exception
      Throw New ApplicationException("extranet.DA.CandidatoDAO.atualizarExperiencia(): " & ex.Message)
    End Try

  End Sub

  Public Function selecionarFormacoesEscolares(ByVal candidato As BECandidato) As List(Of FormacaoEscolar)
    Dim consultaSql As String = "SELECT e.id, e.ano, e.curso, e.datafim, e.datainicio, " & _
                                       "e.escolaridade, e.nomeinstituicao " & _
                                "FROM ext_formacoes_escolares as e " & _
                                "INNER JOIN ext_rel_formacoes_candidato rfc on e.id=rfc.formacaoescolarid " & _
                                "INNER JOIN ext_candidatos c on rfc.candidatoid=c.id AND c.id =@candidatoid"
    Dim formacao As FormacaoEscolar
    Dim formacoes As New List(Of FormacaoEscolar)
    Using command As New MySqlCommand(consultaSql, _conexao)
      command.Parameters.AddWithValue("@candidatoId", candidato.Id)
      Using reader As MySqlDataReader = command.ExecuteReader()
        While reader.Read()
          formacao = New FormacaoEscolar()
          If Not TypeOf (reader(0)) Is System.DBNull Then
            formacao.Id = reader(0)
          End If
          If Not TypeOf (reader(1)) Is System.DBNull Then
            Dim ano As New AnoDoCurso()
            formacao.Ano = AnoDoCurso.Parse(ano.GetType(), DirectCast(reader(1), String))
          End If
          If Not TypeOf (reader(2)) Is System.DBNull Then
            formacao.Curso = reader(2)
          End If
          If Not TypeOf (reader(3)) Is System.DBNull Then
            formacao.DataFim = reader(3)
          End If
          If Not TypeOf (reader(4)) Is System.DBNull Then
            formacao.DataInicio = reader(4)
          End If
          If Not TypeOf (reader(5)) Is System.DBNull Then
            Dim esco As New GrauEscolaridade()
            formacao.Escolaridade = AnoDoCurso.Parse(esco.GetType(), DirectCast(reader(5), String))
          End If
          If Not TypeOf (reader(6)) Is System.DBNull Then
            formacao.NomeInstituicao = reader(6)
          End If
          formacoes.Add(formacao)
        End While
      End Using
    End Using
    Return formacoes
  End Function

  Public Function selecionarIdiomasCandidato(ByVal candidato As BECandidato) As List(Of Idioma)
    Dim idiomas As New List(Of Idioma)
    Dim objIdioma As Idioma
    Try
      Dim strSQL As String = "SELECT * " & _
                             "FROM ext_rel_idiomas_candidato ric " & _
                             "INNER JOIN ext_idioma i on ric.idiomaid=i.id " & _
                             "WHERE candidatoid=" & candidato.Id & ";"
      Using command As New MySqlCommand(strSQL, _conexao)
        command.ExecuteNonQuery()
        Using reader As MySqlDataReader = command.ExecuteReader()
          While reader.Read()
            objIdioma = New Idioma()
            objIdioma.Id = reader(1)
            objIdioma.Nivel = reader(2)
            objIdioma.NomeIdioma = reader(4)
            idiomas.Add(objIdioma)
          End While
        End Using
      End Using
    Catch ex As Exception
      Throw New ApplicationException("extranet.DA.CandidatoDAO.selecionarIdiomasCandidato(): " & ex.Message)
    End Try
    Return idiomas
  End Function

  Public Function inserirFormacaoEscolar(ByVal formacao As FormacaoEscolar) As FormacaoEscolar

    Dim consultaSql As String = "INSERT INTO ext_formacoes_escolares (ano, curso, datafim, datainicio, " & _
                                                                     "escolaridade, nomeinstituicao) " & _
                                "VALUES ('" & formacao.Ano & "', '" & formacao.Curso & "', '" & _
                                formacao.DataFim.ToString("yyyy/MM/dd HH:mm") & "', '" & _
                                formacao.DataInicio.ToString("yyyy/MM/dd HH:mm") & "', '" & _
                                formacao.Escolaridade & "', '" & formacao.NomeInstituicao & "'); " & _
                                "SELECT LAST_INSERT_ID();"
    Try
      Using command As New MySqlCommand(consultaSql, _conexao)
        formacao.Id = command.ExecuteScalar()
      End Using
    Catch ex As Exception
      Throw New ApplicationException("extranet.DA.CandidatoDAO.inserirExperiencia(): " & ex.Message)
    End Try
    Return formacao
  End Function

  Public Sub inserirFormacaoCandidato(ByVal formacao As FormacaoEscolar, ByVal candidato As BECandidato)
    Dim consultasql As String = "insert into ext_rel_formacoes_candidato (candidatoid,formacaoescolarid) values (@candidatoid,@formacaoescolarid)"
    Using command As New MySqlCommand(consultasql, _conexao)
      command.Parameters.Add("@candidatoid", MySqlDbType.Int32).Value = candidato.Id
      command.Parameters.Add("@formacaoescolarid", MySqlDbType.Int32).Value = formacao.Id
      command.ExecuteNonQuery()
    End Using
  End Sub

  Public Sub atualizarFormacaoEscolarDoCandidato(ByVal formacao As FormacaoEscolar, ByVal candidato As BECandidato)
    Dim consultaSql As String = "UPDATE ext_formacoes_escolares set ano=@ano, curso=@curso, " & _
                                "datafim=@datafim, datainicio=@datainicio, escolaridade=@escolaridade, " & _
                                "nomeinstituicao=@nomeinstituicao WHERE id=@id"
    Try
      Using command As New MySqlCommand(consultaSql, _conexao)
        command.Parameters.Add("@ano", MySqlDbType.VarChar).Value = formacao.Ano
        command.Parameters.Add("@curso", MySqlDbType.VarChar).Value = formacao.Curso
        If formacao.DataFim = Date.MinValue Then
          command.Parameters.Add("@datafim", Nothing)
        Else
          command.Parameters.AddWithValue("@datafim", formacao.DataFim)
        End If
        If formacao.DataInicio = Date.MinValue Then
          command.Parameters.Add("@datainicio", Nothing)
        Else
          command.Parameters.AddWithValue("@datainicio", formacao.DataInicio)
        End If
        command.Parameters.Add("@escolaridade", MySqlDbType.VarChar).Value = formacao.Escolaridade
        command.Parameters.Add("@nomeinstituicao", MySqlDbType.VarChar).Value = formacao.NomeInstituicao
        command.Parameters.AddWithValue("@id", formacao.Id)
        command.ExecuteNonQuery()
      End Using
    Catch ex As Exception
      Throw New ApplicationException("extranet.DA.CandidatoDAO.atualizarExperiencia(): " & ex.Message)
    End Try
  End Sub

  Public Sub atualizarIdiomaDoCandidato(ByVal candidato As BECandidato)
    Try
      Dim strSQL As String = "DELETE FROM ext_rel_idiomas_candidato WHERE candidatoid=" & candidato.Id & ";"
      For Each objIdioma As Idioma In candidato.Curriculo.Idiomas
        strSQL &= "INSERT INTO ext_rel_idiomas_candidato (candidatoid, idiomaid, nivel) VALUES(" & _
                  candidato.Id & ", " & objIdioma.Id & ", '" & objIdioma.Nivel & "');"
      Next
      Using command As New MySqlCommand(strSQL, _conexao)
        command.ExecuteNonQuery()
      End Using
    Catch ex As Exception
      Throw New ApplicationException("extranet.DA.CandidatoDAO.atualizarIdiomaDoCandidato(): " & ex.Message)
    End Try
  End Sub

  Public Function inserirExperienciaProfissional(ByVal experiencia As ExperienciaProfissional) As ExperienciaProfissional
    Dim consultaSql As String = "INSERT INTO ext_experiencias_professionais (atribuicoesdocargo, cargo, dataentrada, " & _
                                                                            "datasaida, empregoatual, nomedaempresa) " & _
                                "VALUES (@atribuicoesdocargo,@cargo,@dataentrada,@datasaida,@empregoatual,@nomedaempresa);" & _
                                "SELECT LAST_INSERT_ID();"
    Try
      Using command As New MySqlCommand(consultaSql, _conexao)

        command.Parameters.Add("@atribuicoesdocargo", MySqlDbType.VarChar).Value = experiencia.AtribuicoesDoCargo
        command.Parameters.Add("@cargo", MySqlDbType.VarChar).Value = experiencia.Cargo
        If experiencia.DataEntrada = Date.MinValue Then
          command.Parameters.Add("@dataentrada", Nothing)
        Else
          command.Parameters.AddWithValue("@dataentrada", experiencia.DataEntrada)
        End If
        If experiencia.DataSaida = Date.MinValue Then
          command.Parameters.Add("@datasaida", Nothing)
        Else
          command.Parameters.AddWithValue("@datasaida", experiencia.DataSaida)
        End If
        command.Parameters.Add("@empregoatual", MySqlDbType.VarChar).Value = experiencia.EmpregoAtual
        command.Parameters.Add("@nomedaempresa", MySqlDbType.VarChar).Value = experiencia.NomeDaEmpresa
        experiencia.Id = command.ExecuteScalar()
      End Using
    Catch ex As Exception
      Throw New ApplicationException("extranet.DA.CandidatoDAO.inserirExperiencia(): " & ex.Message)
    End Try
    Return experiencia
  End Function

  Public Sub inserirExperienciaCandidato(ByVal experiencia As ExperienciaProfissional, ByVal candidato As BECandidato)
    Dim consultasql As String = "insert into ext_rel_exp_candidato (candidatoid, experienciaprofissionalid) values (@candidatoid,@experienciaid)"
    Using command As New MySqlCommand(consultasql, _conexao)
      command.Parameters.Add("@candidatoid", MySqlDbType.Int32).Value = candidato.Id
      command.Parameters.Add("@experienciaid", MySqlDbType.Int32).Value = experiencia.Id
      command.ExecuteNonQuery()
    End Using
  End Sub

  Public Sub inserirFormacoesEscolares(ByRef candidato As BECandidato)

  End Sub

  Public Sub inserir(ByRef candidato As BECandidato)
    Dim consultaSql As String = "INSERT INTO ext_candidatos (cep, cidade, cpf, datanascimento, ddd, email, endereco, " & _
                                                            "estado, estadocivil, login, nacionalidade, nome, pais, " & _
                                                            "portadordeficiencia, possuifilhos, ramal, senha, sexo, " & _
                                                            "telefonecelular, telefoneresidencial, rg) " & _
                                                    "VALUES (@cep, @cidade, @cpf, @datanascimento, @ddd, @email, " & _
                                                            "@endereco, @estado, @estadocivil, @login, @nacionalidade, " & _
                                                            "@nome, @pais, @portadordeficiencia, @possuifilhos, @ramal, " & _
                                                            "@senha, @sexo, @telefonecelular, @telefoneresidencial, @rg);" & _
                                 "SELECT LAST_INSERT_ID();"
    Dim idCandidato As Integer
    Try
      Using command As New MySqlCommand(consultaSql, _conexao)
        Console.Write(candidato)
        If candidato.Cep.Length < 1 Then
          command.Parameters.AddWithValue("@cep", Nothing)
        Else
          command.Parameters.Add("@cep", MySqlDbType.VarChar).Value = candidato.Cep
        End If
        command.Parameters.Add("@portadordeficiencia", MySqlDbType.VarChar).Value = candidato.PortadorDeficiencia
        command.Parameters.Add("@possuifilhos", MySqlDbType.VarChar).Value = candidato.PossuiFilhos

        If candidato.Cidade.Length < 1 Then
          command.Parameters.AddWithValue("@cidade", Nothing)
        Else
          command.Parameters.Add("@cidade", MySqlDbType.VarChar).Value = candidato.Cidade
        End If

        If candidato.Cpf.Length < 1 Then
          command.Parameters.AddWithValue("@cpf", Nothing)
        Else
          command.Parameters.Add("@cpf", MySqlDbType.VarChar).Value = candidato.Cpf
        End If

        If candidato.DataNascimento = Date.MinValue Then
          command.Parameters.Add("@datanascimento", Nothing)
        Else
          command.Parameters.AddWithValue("@datanascimento", candidato.DataNascimento)
        End If

        If candidato.Ddd.Length < 1 Then
          command.Parameters.AddWithValue("@ddd", Nothing)
        Else
          command.Parameters.Add("@ddd", MySqlDbType.VarChar).Value = candidato.Ddd
        End If

        If candidato.Email.Length < 1 Then
          command.Parameters.AddWithValue("@email", Nothing)
        Else
          command.Parameters.Add("@email", MySqlDbType.VarChar).Value = candidato.Email
        End If

        If candidato.Endereco.Length < 1 Then
          command.Parameters.AddWithValue("@endereco", Nothing)
        Else
          command.Parameters.Add("@endereco", MySqlDbType.VarChar).Value = candidato.Endereco
        End If

        If candidato.Estado.Length < 1 Then
          command.Parameters.AddWithValue("@estado", Nothing)
        Else
          command.Parameters.Add("@estado", MySqlDbType.VarChar).Value = candidato.Estado
        End If
        If candidato.EstadoCivil.Length < 1 Then
          command.Parameters.AddWithValue("@estadocivil", Nothing)
        Else
          command.Parameters.Add("@estadocivil", MySqlDbType.VarChar).Value = candidato.EstadoCivil
        End If

        If candidato.Login.Length < 1 Then
          command.Parameters.AddWithValue("@login", Nothing)
        Else
          command.Parameters.Add("@login", MySqlDbType.VarChar).Value = candidato.Login
        End If
        If candidato.Nacionalidade.Length < 1 Then
          command.Parameters.AddWithValue("@nacionalidade", Nothing)
        Else
          command.Parameters.Add("@nacionalidade", MySqlDbType.VarChar).Value = candidato.Nacionalidade
        End If
        If candidato.Nome.Length < 1 Then
          command.Parameters.AddWithValue("@nome", Nothing)
        Else
          command.Parameters.Add("@nome", MySqlDbType.VarChar).Value = candidato.Nome
        End If
        If candidato.Pais.Length < 1 Then
          command.Parameters.AddWithValue("@pais", Nothing)
        Else
          command.Parameters.Add("@pais", MySqlDbType.VarChar).Value = candidato.Pais
        End If
        If candidato.Ramal.Length < 1 Then
          command.Parameters.AddWithValue("@ramal", Nothing)
        Else
          command.Parameters.Add("@ramal", MySqlDbType.VarChar).Value = candidato.Ramal
        End If

        If candidato.Senha.Length < 1 Then
          command.Parameters.AddWithValue("@senha", Nothing)
        Else
          command.Parameters.Add("@senha", MySqlDbType.VarChar).Value = candidato.Senha
        End If

        If candidato.Sexo.Length < 1 Then
          command.Parameters.AddWithValue("@sexo", Nothing)
        Else
          command.Parameters.Add("@sexo", MySqlDbType.VarChar).Value = candidato.Sexo
        End If
        If candidato.TelefoneCelular.Length < 1 Then
          command.Parameters.AddWithValue("@telefonecelular", Nothing)
        Else
          command.Parameters.Add("@telefonecelular", MySqlDbType.VarChar).Value = candidato.TelefoneCelular
        End If
        If candidato.TelefoneResidencial.Length < 1 Then
          command.Parameters.AddWithValue("@telefoneresidencial", Nothing)
        Else
          command.Parameters.Add("@telefoneresidencial", MySqlDbType.VarChar).Value = candidato.TelefoneResidencial
        End If
        If candidato.Rg.Length < 1 Then
          command.Parameters.AddWithValue("@rg", Nothing)
        Else
          command.Parameters.Add("@rg", MySqlDbType.VarChar).Value = candidato.Rg
        End If
        idCandidato = command.ExecuteScalar()
      End Using
      candidato.Id = idCandidato
    Catch ex As Exception
      Throw New ApplicationException("extranet.DA.CandidatoDAO.inserir(): " & ex.Message)
    End Try

  End Sub

  Public Sub deletar(ByVal candidato As BECandidato)

  End Sub

  Public Sub atualizar(ByVal candidato As BECandidato)
    Dim consultaSql As String = "update ext_candidatos set cep=@cep, cidade=@cidade, cpf=@cpf, datanascimento=@datanascimento,ddd =@ddd,email=@email,endereco=@endereco,estado=@estado,estadocivil=@estadocivil,login=@login,nacionalidade=@nacionalidade,nome=@nome,nacionalidade=@nacionalidade,pais=@pais,portadordeficiencia = @portadordeficiencia,possuifilhos= @possuifilhos,ramal= @ramal,senha = @senha,sexo= @sexo,telefonecelular= @telefonecelular,telefoneresidencial= @telefoneresidencial , rg = @rg where id = @id"
    Dim registrosAtualizados As Integer
    Try
      Using command As New MySqlCommand(consultaSql, _conexao)
        If candidato.Cep.Length < 1 Then
          command.Parameters.AddWithValue("@cep", Nothing)
        Else
          command.Parameters.Add("@cep", MySqlDbType.VarChar).Value = candidato.Cep
        End If
        command.Parameters.Add("@portadordeficiencia", MySqlDbType.VarChar).Value = candidato.PortadorDeficiencia
        command.Parameters.Add("@possuifilhos", MySqlDbType.VarChar).Value = candidato.PossuiFilhos

        If candidato.Cidade.Length < 1 Then
          command.Parameters.AddWithValue("@cidade", Nothing)
        Else
          command.Parameters.Add("@cidade", MySqlDbType.VarChar).Value = candidato.Cidade
        End If
        If candidato.Cpf.Length < 1 Then
          command.Parameters.AddWithValue("@cpf", Nothing)
        Else
          command.Parameters.Add("@cpf", MySqlDbType.VarChar).Value = candidato.Cpf
        End If
        If candidato.DataNascimento = Date.MinValue Then
          command.Parameters.Add("@datanascimento", Nothing)
        Else
          command.Parameters.AddWithValue("@datanascimento", candidato.DataNascimento)
        End If
        If candidato.Ddd.Length < 1 Then
          command.Parameters.AddWithValue("@ddd", Nothing)
        Else
          command.Parameters.Add("@ddd", MySqlDbType.VarChar).Value = candidato.Ddd
        End If

        If candidato.Email.Length < 1 Then
          command.Parameters.AddWithValue("@email", Nothing)
        Else
          command.Parameters.Add("@email", MySqlDbType.VarChar).Value = candidato.Email
        End If

        If candidato.Endereco.Length < 1 Then
          command.Parameters.AddWithValue("@endereco", Nothing)
        Else
          command.Parameters.Add("@endereco", MySqlDbType.VarChar).Value = candidato.Endereco
        End If

        If candidato.Estado.Length < 1 Then
          command.Parameters.AddWithValue("@estado", Nothing)
        Else
          command.Parameters.Add("@estado", MySqlDbType.VarChar).Value = candidato.Estado
        End If
        If candidato.EstadoCivil.Length < 1 Then
          command.Parameters.AddWithValue("@estadocivil", Nothing)
        Else
          command.Parameters.Add("@estadocivil", MySqlDbType.VarChar).Value = candidato.EstadoCivil
        End If
        command.Parameters.Add("@id", MySqlDbType.Int32).Value = candidato.Id
        If candidato.Login.Length < 1 Then
          command.Parameters.AddWithValue("@login", Nothing)
        Else
          command.Parameters.Add("@login", MySqlDbType.VarChar).Value = candidato.Login
        End If
        If candidato.Nacionalidade.Length < 1 Then
          command.Parameters.AddWithValue("@nacionalidade", Nothing)
        Else
          command.Parameters.Add("@nacionalidade", MySqlDbType.VarChar).Value = candidato.Nacionalidade
        End If
        If candidato.Nome.Length < 1 Then
          command.Parameters.AddWithValue("@nome", Nothing)
        Else
          command.Parameters.Add("@nome", MySqlDbType.VarChar).Value = candidato.Nome
        End If
        If candidato.Pais.Length < 1 Then
          command.Parameters.AddWithValue("@pais", Nothing)
        Else
          command.Parameters.Add("@pais", MySqlDbType.VarChar).Value = candidato.Pais
        End If

        If candidato.Ramal.Length < 1 Then
          command.Parameters.AddWithValue("@ramal", Nothing)
        Else
          command.Parameters.Add("@ramal", MySqlDbType.VarChar).Value = candidato.Ramal
        End If

        If candidato.Senha.Length < 1 Then
          command.Parameters.AddWithValue("@senha", Nothing)
        Else
          command.Parameters.Add("@senha", MySqlDbType.VarChar).Value = candidato.Senha
        End If

        If candidato.Sexo.Length < 1 Then
          command.Parameters.AddWithValue("@sexo", Nothing)
        Else
          command.Parameters.Add("@sexo", MySqlDbType.VarChar).Value = candidato.Sexo
        End If
        If candidato.TelefoneCelular.Length < 1 Then
          command.Parameters.AddWithValue("@telefonecelular", Nothing)
        Else
          command.Parameters.Add("@telefonecelular", MySqlDbType.VarChar).Value = candidato.TelefoneCelular
        End If
        If candidato.TelefoneResidencial.Length < 1 Then
          command.Parameters.AddWithValue("@telefoneresidencial", Nothing)
        Else
          command.Parameters.Add("@telefoneresidencial", MySqlDbType.VarChar).Value = candidato.TelefoneResidencial
        End If
        If candidato.Rg.Length < 1 Then
          command.Parameters.AddWithValue("@rg", Nothing)
        Else
          command.Parameters.Add("@rg", MySqlDbType.VarChar).Value = candidato.Rg
        End If
        registrosAtualizados = command.ExecuteNonQuery()

      End Using

      Me.salvarCurriculoCandidato(candidato)

    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.CandidatoDAO.atualizar(): " & ex.Message)
    End Try

  End Sub

  Public Function loginExiste(ByVal candidato As BECandidato) As Boolean
    Dim consultaSql As String = "SELECT COUNT(*) FROM ext_candidatos WHERE login=@login"
    Dim existe As Boolean = False
    Try
      Using command As New MySqlCommand(consultaSql, _conexao)
        command.Parameters.Add("@login", MySql.Data.MySqlClient.MySqlDbType.VarChar).Value = candidato.Login
        Using reader As MySqlDataReader = command.ExecuteReader()
          While reader.Read()
            If reader.GetInt32(0) > 0 Then
              existe = True
            End If
          End While
        End Using
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.CandidatoDAO.selecionar(): " & ex.Message)
    End Try
    Return existe
  End Function

  Public Function cpfExiste(ByVal candidato As BECandidato)
    Dim consultaSql As String = "SELECT COUNT (*) FROM ext_candidatos WHERE cpf=@cpf"
    Dim existe As Boolean = False
    Try
      Using command As New MySqlCommand(consultaSql, _conexao)
        command.Parameters.Add("@cpf", MySql.Data.MySqlClient.MySqlDbType.VarChar).Value = candidato.Cpf

        Using reader As MySqlDataReader = command.ExecuteReader()
          While reader.Read()
            Console.Write(reader)
            If reader.GetInt32(0) > 0 Then
              existe = True
            End If
          End While
        End Using
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.CandidatoDAO.selecionar(): " & ex.Message)
    End Try
    Return existe
  End Function

End Class